package com.dzzw.Dao;

import com.dzzw.Model.Option;
import com.dzzw.utils.DButils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

//投票选项
public class OptionDao {
    private final Connection con = DButils.getCon();
    //新增投票选项
    public void OptionAdd(Option option){
        String sql = "insert into t_option(vote_id,option_title) value(?,?)";
        try{
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1, option.getVote_id());
            preparedStatement.setString(2, option.getOption_title());
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        } catch(Exception e) {
            System.out.println("数据添加失败！");

            e.printStackTrace();
        }
    }

    //修改投票选项
    public void OptionModify(Option option){
        String sql = "update t_option set option_title=? where option_id=?";
        try{
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setString(1, option.getOption_title());
            preparedStatement.setInt(2, option.getVote_id());
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        } catch(Exception e) {
            System.out.println("数据修改失败！");

            e.printStackTrace();
        }
    }

    //删除投票选项
    public void OptionDelete(int option_id){
        String sql = "Delete from t_option where option_id = ?";
        try{
            PreparedStatement preparedStatement = con.prepareStatement(sql);
            preparedStatement.setInt(1, option_id);
            preparedStatement.executeUpdate();
            preparedStatement.close();
            con.close();
        } catch(Exception e) {
            System.out.println("数据修改失败！");

            e.printStackTrace();
        }
    }

    //查询投票主题下投票选项的票数
    public static List<Option> SelectAllOption(){
        List<Option> list = new ArrayList<>();
        String sql = "SELECT o.option_id,o.option_title,v.vote_title FROM t_option AS o INNER JOIN t_vote AS v ON o.vote_id = v.vote_id";
        try{
            Connection con =DButils.getCon();
            PreparedStatement preparedStatement =con.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Option option = new Option();
                option.setOption_id(resultSet.getInt(1));
                option.setVote_title(resultSet.getString(2));
                option.setOption_title(resultSet.getString(3));
                list.add(option);
            }
            resultSet.close();
            preparedStatement.close();
            con.close();
            System.out.println("查询所有数据成功！");
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }

    //查询投票主题下投票选项的票数
    public static List<Option> SelectAllOptionByVoteId(int vote_id){
        List<Option> list = new ArrayList<>();
        String sql = "SELECT o.option_id,o.option_title,v.vote_title FROM t_option AS o INNER JOIN t_vote AS v ON o.vote_id = v.vote_id where o.vote_id = ?";
        try{
            Connection con =DButils.getCon();
            PreparedStatement preparedStatement =con.prepareStatement(sql);
            preparedStatement.setInt(1,vote_id);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                Option option = new Option();
                option.setOption_id(resultSet.getInt(1));
                option.setVote_title(resultSet.getString(2));
                option.setOption_title(resultSet.getString(3));
                list.add(option);
            }
            resultSet.close();
            preparedStatement.close();
            con.close();
            System.out.println("查询所有数据成功！");
        }catch (Exception e){
            e.printStackTrace();
        }
        return list;
    }
}
